package com.kelvem.saas.workbanch.core.manager.service;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.TypeReference;
import com.kelvem.saas.workbanch.core.base.Page;
import com.kelvem.saas.workbanch.core.exception.SaasAssert;
import com.kelvem.saas.workbanch.core.exception.SaasException;
import com.kelvem.saas.workbanch.core.manager.dao.GraphqlDao;
import com.kelvem.saas.workbanch.core.utils.StringUtil;
import jakarta.annotation.Resource;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 实体Service
 *
 * @author kelvem
 * @version 1.0
 */
@Service
@Slf4j
public class GraphqlService {

    @Resource
    private GraphqlDao graphqlDao;

    /**
     * 查询实体列表数据
     *
     * @param queryVo 查询用
     * @return List<Map<String, Object>>
     */
    public List<Map<String, Object>> queryEntityList(String tableName, Map<String, Object> queryVo) {

        log.info("GraphqlService:queryEntityList:start, tableName={}, queryVo=【{}】", tableName, queryVo);
        SaasAssert.notNull(tableName);
        SaasAssert.notNull(queryVo);

        try {
    
            Map<String, String> map = new HashMap<>();
            String sql = "select * from " + tableName;
            String where = "";
            for (String key : queryVo.keySet()) {
                if (key.equalsIgnoreCase("sortBy") || key.equalsIgnoreCase("sortType")) {
                    continue;
                }
                if (key.equalsIgnoreCase("pageNum") || key.equalsIgnoreCase("pageSize")) {
                    continue;
                }
                if (queryVo.get(key) == null 
                        || StringUtil.isEmpty(queryVo.get(key).toString())) {
                    continue;
                }
                where += " and " + StringUtil.aaaAaaToaaa_aaa(key) + " = '" + queryVo.get(key) + "'";
            }
            if (where.length() > 0) {
                where = " where " + where.substring(5);
            }
            if (queryVo.get("sortBy") != null && StringUtil.isNotEmpty(queryVo.get("sortBy").toString())) {
                if (queryVo.get("sortType") != null && StringUtil.isNotEmpty(queryVo.get("sortType").toString())) {
                    sql = sql + where + " order by " + StringUtil.aaaAaaToaaa_aaa(queryVo.get("sortBy").toString()) + " " + StringUtil.aaaAaaToaaa_aaa(queryVo.get("sortType").toString());
                } else {
                    sql = sql + where + " order by " + StringUtil.aaaAaaToaaa_aaa(queryVo.get("sortBy").toString()) + " desc";
                }
            } else {
                sql = sql + where + " order by id desc";
            }

            map.put("sql", sql);
            List<Map<String, Object>> list = graphqlDao.query(map);

            // convert
            List<Map<String, Object>> result = new ArrayList<>();
            for (Map<String, Object> buf :
                    list) {

                Map<String, Object> row = new HashMap<>();
                for (String key :
                        buf.keySet()) {
                    row.put(StringUtil.aaa_aaaToaaaAaa(key), buf.get(key));
                }
                result.add(row);
            }

            log.info("GraphqlService:queryEntityList:end, list.size={}", result.size());
            return result;
        } catch (Exception e) {
            String msg = String.format("GraphqlService:queryEntityList:error, %s", e.getMessage());
            log.error(msg, e);
            throw new SaasException(msg);
        }
    }

    /**
     * 查询实体分页数据
     *
     * @param queryVo 查询用
     * @return Page<Map<String, Object>>
     */
    public Page<Map<String, Object>> queryEntityPage(String tableName, Map<String, Object> queryVo) {

        log.info("GraphqlService:queryEntityPage:start, tableName={}, queryVo=【{}】", tableName, queryVo);
        SaasAssert.notNull(tableName);
        SaasAssert.notNull(queryVo);

        try {
            Map<String, String> map = new HashMap<>();

            // select
            String select = "select * from " + tableName;

            // pageSize & pageNum
            Object pageNumObj = queryVo.getOrDefault("pageNum", 1);
            queryVo.remove("pageNum");
            int pageNum = Integer.parseInt(pageNumObj.toString());

            Object pageSizeObj = queryVo.getOrDefault("pageSize", 3);
            queryVo.remove("pageSize");
            int pageSize = Integer.parseInt(pageSizeObj.toString());
            pageSize = pageSize > 999 ? 999 : pageSize;

            // where
            String where = "";
            for (String key : queryVo.keySet()) {
                if (key.equalsIgnoreCase("sortBy") || key.equalsIgnoreCase("sortType")) {
                    continue;
                }
                if (queryVo.get(key) == null
                        || StringUtil.isEmpty(queryVo.get(key).toString())) {
                    continue;
                }
                where += " and " + StringUtil.aaaAaaToaaa_aaa(key) + " = '" + queryVo.get(key) + "'";
            }
            if (where.length() > 0) {
                where = " where " + where.substring(5);
            }

            // orderby
            String orderby = "";
            if (queryVo.get("sortBy") != null && StringUtil.isNotEmpty(queryVo.get("sortBy").toString())) {
                if (queryVo.get("sortType") != null && StringUtil.isNotEmpty(queryVo.get("sortType").toString())) {
                    orderby =  " order by " + StringUtil.aaaAaaToaaa_aaa(queryVo.get("sortBy").toString()) + " " + StringUtil.aaaAaaToaaa_aaa(queryVo.get("sortType").toString());
                } else {
                    orderby = " order by " + StringUtil.aaaAaaToaaa_aaa(queryVo.get("sortBy").toString()) + " desc";
                }
            } else {
                orderby = " order by id desc";
            }

            // limit
            String limit = " limit " + pageSize + " offset " + (pageNum - 1) * pageSize;

            // execute
            map.put("sql", select + where + orderby + limit);
            List<Map<String, Object>> list = graphqlDao.query(map);

            // convert
            List<Map<String, Object>> result = new ArrayList<>();
            for (Map<String, Object> buf :
                    list) {

                Map<String, Object> row = new HashMap<>();
                for (String key :
                        buf.keySet()) {
                    row.put(StringUtil.aaa_aaaToaaaAaa(key), buf.get(key));
                }
                result.add(row);
            }

            // total
            map = new HashMap<>();
            map.put("sql", "select count(*) as totalNum from " + tableName + where);
            Map<String, Object> countInfo = graphqlDao.get(map);
            Object totalNumObj = countInfo.getOrDefault("totalNum", 0);
            Long totalNum = Long.parseLong(totalNumObj.toString());

            // page
            Page<Map<String, Object>> page = new Page<>(pageNum, pageSize);
            page.setTotal(totalNum);
            page.setRecords(result);

            log.info("GraphqlService:queryEntityPage:end, page.size={}", page.getSize());
            return page;
        } catch (Exception e) {
            String msg = String.format("GraphqlService:queryEntityPage:error, %s", e.getMessage());
            log.error(msg, e);
            throw new SaasException(msg);
        }
    }

    /**
     * 根据id查询实体
     *
     * @param tableName 表明
     * @param entityId 实体id
     * @return Map<String, Object>
     */
    public Map<String, Object> getEntityById(String tableName, Long entityId) {

        log.info("GraphqlService:getEntityById:start, tableName={}, entityId={}", tableName, entityId);
        SaasAssert.notNull(tableName);
        SaasAssert.notNull(entityId);
        
        try {
            Map<String, String> map = new HashMap<>();
            String sql = "select * from " + tableName + " where id = " + entityId;
            map.put("sql", sql);
            Map<String, Object> entity = graphqlDao.get(map);

            // convert
            Map<String, Object> result = new HashMap<>();
            for (String key :
                    entity.keySet()) {
                result.put(StringUtil.aaa_aaaToaaaAaa(key), entity.get(key));
            }
            log.info("GraphqlService:getEntityById:end, entityInfo=【{}】", result);
            
            return result;
        } catch (Exception e) {
            String msg = String.format("GraphqlService:getEntityById:error, %s", e.getMessage());
            log.error(msg, e);
            throw new SaasException(msg);
        }
    }

    /**
     * 保存实体列表数据
     *
     * @param entity 实体
     * @return 影响行数
     */
    public Integer addEntity(String tableName, Map<String, Object> entity) {

        log.info("GraphqlService:addEntity:start, tableName={}, queryVo=【{}】", tableName, entity);
        SaasAssert.notNull(tableName);
        SaasAssert.notNull(entity);

        try {
            // INSERT INTO t1(field1,field2)VALUE(v001,v002)
            String column = "";
            String value = "";
            for (String key :
                    entity.keySet()) {
                if ("id".equalsIgnoreCase(key)) {
                    continue;
                }
                column += ", " + StringUtil.aaaAaaToaaa_aaa(key);
                value += ", '" + entity.get(key) + "'";
            }

            if (column.length() > 0) {
                column = column.substring(1);
                value = value.substring(1);
            }
            String sql = "INSERT INTO " + tableName + "(" + column + ") VALUE (" + value + ");";

            Map<String, String> map = new HashMap<>();
            map.put("sql", sql);
            Integer n = graphqlDao.add(map);
            log.info("GraphqlService:addEntity:end, insert.size={}", n);
            return 1;
        } catch (Exception e) {
            String msg = String.format("GraphqlService:addEntity:error, %s", e.getMessage());
            log.error(msg, e);
            throw new SaasException(msg);
        }
    }

    /**
     * 更新实体列表数据
     *
     * @param entity 实体
     * @return 影响行数
     */
    public Integer updateEntity(String tableName, Map<String, Object> entity) {

        log.info("GraphqlService:updateEntity:start, tableName={}, queryVo=【{}】", tableName, entity);
        SaasAssert.notNull(tableName);
        SaasAssert.notNull(entity);

        try {
            // update t1 set field1=v001, field2=v002 where id=1
            String set = "";
            for (String key :
                    entity.keySet()) {
                set += ", " + StringUtil.aaaAaaToaaa_aaa(key) + " = \"" + entity.get(key) + "\"";
            }

            if (set.length() > 0) {
                set = set.substring(1);
            }
            String sql = "update " + tableName + " set " + set + " where id=" + entity.get("id") + ";";

            Map<String, String> map = new HashMap<>();
            map.put("sql", sql);
            Integer n = graphqlDao.add(map);
            log.info("GraphqlService:updateEntity:end, insert.size={}", n);
            return 1;
        } catch (Exception e) {
            String msg = String.format("GraphqlService:updateEntity:error, %s", e.getMessage());
            log.error(msg, e);
            throw new SaasException(msg);
        }
    }

    /**
     * 保存实体列表数据
     *
     * @param entityList 实体列表
     * @return 影响行数
     */
    public Integer batchAddUpdateEntity(String tableName, List<Map<String, Object>> entityList) {

        log.info("GraphqlService:batchAddUpdateEntity:start, tableName={}, queryVo=【{}】", tableName, entityList);
        SaasAssert.notNull(tableName);
        SaasAssert.notNull(entityList);
        SaasAssert.notEmpty(entityList, "实体列表");

        try {

            for (Map<String, Object> entity:
                 entityList) {
                Object id = entity.get("id");
                if (id == null) {
                    this.addEntity(tableName, entity);
                } else {
                    this.updateEntity(tableName, entity);
                }
            }
            log.info("GraphqlService:batchAddUpdateEntity:end, insert.size={}", entityList.size());
            return entityList.size();
        } catch (Exception e) {
            String msg = String.format("GraphqlService:batchAddUpdateEntity:error, %s", e.getMessage());
            log.error(msg, e);
            throw new SaasException(msg);
        }
    }

    /**
     * 根据id删除实体
     *
     * @param tableName 表明
     * @param entityId 实体id
     * @return int count
     */
    public Integer deleteEntity(String tableName, Long entityId) {

        log.info("GraphqlService:deleteEntity:start, tableName={}, entityId={}", tableName, entityId);
        SaasAssert.notNull(tableName);
        SaasAssert.notNull(entityId);

        try {
            Map<String, String> map = new HashMap<>();
            String sql = "delete from " + tableName + " where id = " + entityId;
            map.put("sql", sql);
            Integer count = graphqlDao.execute(map);

            log.info("GraphqlService:deleteEntity:end, count=【{}】", count);

            return count;
        } catch (Exception e) {
            String msg = String.format("GraphqlService:deleteEntity:error, %s", e.getMessage());
            log.error(msg, e);
            throw new SaasException(msg);
        }
    }

    /**
     * select
     *
     * @param sql
     * @return List<Map<String, Object>>
     */
    public List<Map<String, Object>> select(String sql) {

        log.info("GraphqlService:select:start, sql=【{}】", sql);
        SaasAssert.notNull(sql);

        try {

            Map<String, String> map = new HashMap<>();
            map.put("sql", sql);
            List<Map<String, Object>> list = graphqlDao.query(map);

            // convert
            List<Map<String, Object>> result = new ArrayList<>();
            for (Map<String, Object> buf :
                    list) {

                Map<String, Object> row = new HashMap<>();
                for (String key :
                        buf.keySet()) {
                    row.put(StringUtil.aaa_aaaToaaaAaa(key), buf.get(key));
                }
                result.add(row);
            }

            log.info("GraphqlService:select:end, list.size={}", result.size());
            return result;
        } catch (Exception e) {
            String msg = String.format("GraphqlService:select:error, %s", e.getMessage());
            log.error(msg, e);
            throw new SaasException(msg);
        }
    }

    /**
     * select
     *
     * @param sql
     * @return List<Map<String, Object>>
     */
    public <T> List<T> selectT(String sql, Class<T> clazz) {

        log.info("GraphqlService:selectT:start, sql=【{}】, clazz={}", sql, clazz);
        SaasAssert.notNull(sql);
        SaasAssert.notNull(clazz);

        try {
            Map<String, String> map = new HashMap<>();
            map.put("sql", sql);
            List<Map<String, Object>> listMap = graphqlDao.query(map);
            if (listMap == null || listMap.isEmpty()) {
                return new ArrayList<>();
            }

            String json = JSON.toJSONString(listMap);
            List<T> list = JSON.parseArray(json, clazz);

            log.info("GraphqlService:selectT:end, list.size={}", list.size());
            return list;
        } catch (Exception e) {
            String msg = String.format("GraphqlService:selectT:error, %s", e.getMessage());
            log.error(msg, e);
            throw new SaasException(msg);
        }
    }

    /**
     * keyValue, options用
     *
     * @param tableName 表名
     * @param keyColumn key字段名
     * @param valueColumn name字段名
     * @param query 查询条件
     * @param limit 限制数量
     * @return List<Map<String, String>> key-value结果
     */
    public List<Map<String, String>> keyValue(String tableName, String keyColumn, String valueColumn, String query, Integer limit) {

        log.info("GraphqlService:keyValue:start, tableName=【{}】, keyColumn=【{}】, valueColumn=【{}】, query=【{}】, limit=【{}】", tableName, keyColumn, valueColumn, query, limit);
        SaasAssert.notNull(tableName);
        SaasAssert.notNull(keyColumn);
        SaasAssert.notNull(valueColumn);
        if (query == null) {
            query = "";
        }

        try {
            String sql = "select " + keyColumn + " as `key`, " + valueColumn + " as `value` "
                    + " from " + tableName
                    + " where CAST(" + valueColumn + " AS CHAR) like '%" + query + "%' "
                    + " order by " + valueColumn + " limit " + limit;
            Map<String, String> map = new HashMap<>();
            map.put("sql", sql);
            List<Map<String, Object>> listMap = graphqlDao.query(map);
            if (listMap == null || listMap.isEmpty()) {
                return new ArrayList<>();
            }

            String json = JSON.toJSONString(listMap);
            List<Map<String, String>> list = JSON.parseObject(json, new TypeReference<List<Map<String, String>>>() {});

            log.info("GraphqlService:keyValue:end, list.size={}", list.size());
            return list;
        } catch (Exception e) {
            String msg = String.format("GraphqlService:keyValue:error, %s", e.getMessage());
            log.error(msg, e);
            throw new SaasException(msg);
        }
    }


    /**
     * replace mapping表数据
     * 
     * @param tableName 表名
     * @param srcId 源id
     * @param targetIdList 目标ids
     * @param srcColumn 源字段名
     * @param targetColumn 目标字段名
     * @return 影响条数
     */
    public Integer replaceMapping(String tableName, Long srcId, List<Long> targetIdList, String srcColumn, String targetColumn) {

        log.info("GraphqlService:replaceMapping:start, tableName=【{}】, srcId=【{}】, targetIdList=【{}】, srcColumn=【{}】, targetColumn=【{}】", tableName, srcId, targetIdList, srcColumn, targetColumn);
        SaasAssert.notNull(tableName);
        SaasAssert.notNull(srcId);
        if (targetIdList == null) {
            targetIdList = new ArrayList<>();
        }
        if (srcColumn == null || srcColumn.trim().isEmpty() || targetColumn == null || targetColumn.trim().isEmpty()) {
            int indexLeft = tableName.toLowerCase().indexOf("_mapping_");
            if (indexLeft <= 0) {
                throw new SaasException("未标识字段名称, 且映射表名无法推断, mergeMapping接口调用失败");
            }
            if (srcColumn == null || srcColumn.trim().isEmpty()) {
                srcColumn = tableName.toLowerCase().substring(0, indexLeft).replace("sys_", "") + "_id";
            }
            if (targetColumn == null || targetColumn.trim().isEmpty()) {
                targetColumn = tableName.toLowerCase().substring(indexLeft + 9) + "_id";
            }
        }

        try {
            // select
            String sqlSelect = "select " + targetColumn + " as `target_id` "
                    + " from " + tableName
                    + " where " + srcColumn + " = " + srcId;
            Map<String, String> map = new HashMap<>();
            map.put("sql", sqlSelect);
            List<Map<String, Object>> listMap = graphqlDao.query(map);

            Map<Long, Long> dbTargetIdMap = new HashMap<>();
            for (Map<String, Object> row : listMap) {
                Long dbTargetId = Long.parseLong(row.get("target_id").toString());
                dbTargetIdMap.put(dbTargetId, dbTargetId);
            }

            // add
            for (Long targetId : targetIdList) {
                if (!dbTargetIdMap.containsKey(targetId)) {
                    // 不存在则添加
                    Map<String, Object> entity = new HashMap<>();
                    entity.put(srcColumn, srcId);
                    entity.put(targetColumn, targetId);
                    this.addEntity(tableName, entity);
                }
            }

            // delete
            for (Long dbTargetId : dbTargetIdMap.keySet()) {
                if (!targetIdList.contains(dbTargetId)) {
                    // db不存在则删除
                    String sqlDelete = "delete from " + tableName + " where " + srcColumn + " = " + srcId + " and " + targetColumn + " = " + dbTargetId + ";";
                    Map<String, String> mapDelete = new HashMap<>();
                    mapDelete.put("sql", sqlDelete);
                    graphqlDao.execute(mapDelete);
                }
            }

            // update(无需该操作)
            return 1;
        } catch (Exception e) {
            String msg = String.format("GraphqlService:replaceMapping:error, %s", e.getMessage());
            log.error(msg, e);
            throw new SaasException(msg);
        }
    }
}
